package com.easidea.tim.dao.purchase;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.springside.core.dao.extend.HibernateEntityExtendDao;

import com.easidea.tim.entity.purchase.PurchaseOrder;
import com.easidea.tim.entity.purchase.PurchaseOrderItem;
import com.easidea.tim.entity.purchase.PurchaseReturn;
import com.easidea.tim.entity.purchase.PurchaseReturnItem;
import com.easidea.tim.util.DateUtil;

public class PurchaseOrderDao extends HibernateEntityExtendDao<PurchaseOrder> {
	/**
	 * 按产品统计
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> statPurchaseOrderByProduct(Date startDate,
			Date endDate) {
		String hql = "select item.productName, sum(item.totalArrival) as totalArrival,sum(item.totalArrival*item.productPurchasePrice) as totalAmout"
				+ " from "
				+ PurchaseOrderItem.class.getName()
				+ " as item,"
				+ PurchaseOrder.class.getName()
				+ " as order where  order.deleted = 0 and  order.id = item.purchaseOrder.id  and order.createTime >=:startDate and order.createTime <=:endDate  group by item.productId ";

		Query query = this.getSession().createQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		return query.list();

	}

	/**
	 * 按供应商统计
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> statPurchaseOrderBySupplier(Date startDate,
			Date endDate) {
		String hql = "select po.supplier.name, sum(po.totalMeter) as totalArrival,sum(po.totalAmount) as totalAmout"
				+ " from  "
				+ PurchaseOrder.class.getName()
				+ " as po where  po.deleted = 0 and  po.createTime >=:startDate and po.createTime <=:endDate  group by po.supplier.id ";

		Query query = this.getSession().createQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		return query.list();
	}

	public List<Object[]> statPurchaseOrderByProductTest(Date startDate,
			Date endDate) {
		String hql = "select ttable.pid,ttable.product_name,sum(totalArrival) as totalmeterNum,sum(totalAmout) as totalMoneyNum from ((select item.product_id as pid,item.product_name,item.product_id, sum(item.total_arrival) as totalArrival,sum(item.total_arrival*item.product_purchase_price) as totalAmout " 
				+ "from tim.tim_purchase_order_item"
				+ " as item,tim.tim_purchase_order as order1 where order1.deleted = 0 and order1.id = item.purchase_order_id and order1.create_time >=:startDate and order1.create_time <=:endDate  group by item.product_id)"
				+ " UNION (select item2.product_id as pid,item2.product_name,item2.product_id, sum(item2.total_meter*-1) as totalArrival,sum(item2.total_meter*item2.unit_price*-1) as totalAmout from tim.tim_purchase_return_item"
				+ " as item2,tim.tim_purchase_return as order2 where order2.deleted = 0 and order2.status=3 and order2.id = item2.purchase_return_id and order2.modified_time >=:startDate and order2.modified_time <=:endDate group by item2.product_id)) ttable group by ttable.product_id";

		Query query = this.getSession().createSQLQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		return query.list();

	}
	
	public List<Object[]> statPurchaseOrderBySupplierTest(Date startDate,
			Date endDate) {
		String hql = "select ttable.sid,ttable.sname,sum(totalArrival) as totalmeterNum,sum(totalAmout) as totalMoneyNum from ((select order1.supplier_id as sid,(select name from tim_supply_supplier where id= order1.supplier_id) as sname, sum(item.total_arrival) as totalArrival,sum(item.total_arrival*item.product_purchase_price) as totalAmout " 
				+ "from tim.tim_purchase_order_item"
				+ " as item,tim.tim_purchase_order as order1 where order1.deleted = 0 and order1.id = item.purchase_order_id and order1.create_time >=:startDate and order1.create_time <=:endDate  group by order1.supplier_id)"
				+ " UNION (select order2.supplyer_id as sid,(select name from tim_supply_supplier where id=order2.supplyer_id) as sname, sum(item2.total_meter*-1) as totalArrival,sum(item2.total_meter*item2.unit_price*-1) as totalAmout from tim.tim_purchase_return_item"
				+ " as item2,tim.tim_purchase_return as order2 where order2.deleted = 0 and order2.status=3 and order2.id = item2.purchase_return_id and order2.modified_time >=:startDate and order2.modified_time <=:endDate group by order2.supplyer_id)) ttable group by ttable.sid";

		Query query = this.getSession().createSQLQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		return query.list();

	}
	
	public List<Object[]> getproductDetailSimple(Date startDate,Date endDate,String name){
		String hql = "select item.product_name,item.product_color_code, sum(item.total_arrival) as totalArrival,sum(item.total_arrival*item.product_purchase_price) as totalAmout " 
			+ "from tim.tim_purchase_order_item"
			+ " as item,tim.tim_purchase_order as order1 where order1.deleted = 0 and order1.id = item.purchase_order_id and item.product_name=:productname and order1.create_time >=:startDate and order1.create_time <=:endDate  group by item.product_id"
			+ " UNION select item2.product_name,item2.product_color, sum(item2.total_meter*-1) as totalArrival,sum(item2.total_meter*item2.unit_price*-1) as totalAmout from tim.tim_purchase_return_item"
			+ " as item2,tim.tim_purchase_return as order2 where order2.deleted = 0 and order2.status=3 and order2.id = item2.purchase_return_id and item2.product_name=:productname and order2.modified_time >=:startDate and order2.modified_time <=:endDate group by item2.product_id";

	
	Query query = this.getSession().createSQLQuery(hql);
	Date sDate = DateUtil.getStartTime(startDate);
	Date eDate = DateUtil.getEndTime(endDate);
	query.setString("productname", name);
	query.setString("startDate", DateUtil.formatDateTime(sDate));
	query.setString("endDate", DateUtil.formatDateTime(eDate));
	return query.list();
	}
	
	public List<Object[]> getsuppliyDetailSimple(Date startDate,Date endDate,String id){
		String hql = "select item.product_name,item.product_color_code, sum(item.total_arrival) as totalArrival,sum(item.total_arrival*item.product_purchase_price) as totalAmout " 
			+ "from tim.tim_purchase_order_item"
			+ " as item,tim.tim_purchase_order as order1 where order1.deleted = 0 and order1.id = item.purchase_order_id and order1.supplier_id=:pid and order1.create_time >=:startDate and order1.create_time <=:endDate  group by item.product_id"
			+ " UNION select item2.product_name,item2.product_color, sum(item2.total_meter*-1) as totalArrival,sum(item2.total_meter*item2.unit_price*-1) as totalAmout from tim.tim_purchase_return_item"
			+ " as item2,tim.tim_purchase_return as order2 where order2.deleted = 0 and order2.status=3 and order2.id = item2.purchase_return_id and order2.supplyer_id=:pid and order2.modified_time >=:startDate and order2.modified_time <=:endDate group by item2.product_id";

	
	Query query = this.getSession().createSQLQuery(hql);
	Date sDate = DateUtil.getStartTime(startDate);
	Date eDate = DateUtil.getEndTime(endDate);
	query.setString("pid", id);
	query.setString("startDate", DateUtil.formatDateTime(sDate));
	query.setString("endDate", DateUtil.formatDateTime(eDate));
	return query.list();
	}
	
	

	/**
	 * 按月份统计
	 * 
	 * @param year
	 * @param month
	 * @return
	 */
	public List<Object[]> statPurchaseOrderByMonth(int year, int month) {
		Date startDate = DateUtil.getFirstDayOfMonth(year, month);
		Date endDate = DateUtil.getLastDayOfMonth(year, month);
		String hql = "select item.productArrivalDate, count(po.id)"
				+ " from  "
				+ PurchaseOrderItem.class.getName()
				+ " as item,"
				+ PurchaseOrder.class.getName()
				+ " as po where po.deleted = 0 and  po.id = item.purchaseOrder.id  and po.status = false and  item.productArrivalDate >=:startDate and item.productArrivalDate <=:endDate  group by item.productArrivalDate ";

		Query query = this.getSession().createQuery(hql);
		Date sDate = DateUtil.getStartTime(startDate);
		Date eDate = DateUtil.getEndTime(endDate);
		query.setString("startDate", DateUtil.formatDateTime(sDate));
		query.setString("endDate", DateUtil.formatDateTime(eDate));
		return query.list();
	}

	public PurchaseOrder getPOByFlowNo(String flowNo) {
		if (flowNo != null) {
			Criteria cte = createUnDeletedCriteria();
			cte.add(Restrictions.eq("flowNo", flowNo.trim()));
			PurchaseOrder po = (PurchaseOrder) cte.uniqueResult();
			return po;
		}
		return null;
	}

	/**
	 * 获取采购合同流水号和Id
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getAllFlowNos() {
		String hql = " select flowNo,id from " + PurchaseOrder.class.getName()
				+ " as p where p.deleted = 0";
		return this.getHibernateTemplate().find(hql);
	}

	public PurchaseOrder getPOByPon(String pon) {
		if (StringUtils.isNotBlank(pon)) {
			Criteria cte = createUnDeletedCriteria();
			cte.add(Restrictions.eq("purchaseOrderNum", pon));
			return (PurchaseOrder) cte.uniqueResult();
		} else
			return null;
	}
}
